﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsFormsApplication1
{
    class DBConnection
    {
        string connection = System.Configuration.ConfigurationManager.ConnectionStrings["live"].ConnectionString;

        public void RunInsert(string Directory)
        {
            using (SqlConnection conn = new SqlConnection(connection))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("dbo.sp_InsertFolder");
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@FolderName";
                param.Value = Directory;
                cmd.Parameters.Add(param);
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                conn.Close();
                
            }
        }

        public void RunDelete(string Directory)
        {
            using (SqlConnection conn = new SqlConnection(connection))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("dbo.sp_DeleteFolder");

                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@FolderName";
                param.Value = Directory;
                cmd.Parameters.Add(param);

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                conn.Close();

            }
        }

        public void RunErrorInsert(string Directory)
        {
            using (SqlConnection conn = new SqlConnection(connection))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("dbo.sp_InsertErrorFolder");

                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@FolderName";
                param.Value = Directory;
                cmd.Parameters.Add(param);

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                conn.Close();

            }
        }

        public void RunTrucnate(string TableName)
        {
            using (SqlConnection conn = new SqlConnection(connection))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("dbo.sp_Truncate", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@TableName";
                param.Value = TableName;
                cmd.Parameters.Add(param);

                //cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
                conn.Close();

            }
        }

        public Int32 GetCount(string SQL)
        {
            var dataSet = new DataSet();

            using (SqlConnection conn = new SqlConnection(connection))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL, conn);
                //cmd.Connection = conn;
                var dataAdapter = new SqlDataAdapter { SelectCommand = cmd };
                dataAdapter.Fill(dataSet);
                conn.Close();
                return Convert.ToInt32(dataSet.Tables[0].Rows[0]["CountValue"]);

            }
        }

        public string GetNextDirectory(string CurrentDirectory)
        {
            var dataSet = new DataSet();

            using (SqlConnection conn = new SqlConnection(connection))
            {
                conn.Open();
               // SqlCommand cmd = new SqlCommand("EXEC dbo.sp_GetNextDirectory @CurrentFolder = '" + CurrentDirectory + "'", conn);

                SqlCommand cmd = new SqlCommand("dbo.sp_GetNextDirectory ", conn);
                //cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                var dataAdapter = new SqlDataAdapter { SelectCommand = cmd };
                dataAdapter.SelectCommand.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@CurrentFolder",
                    Value = CurrentDirectory,
                    SqlDbType = SqlDbType.NVarChar,
                    Size = 500  // Assuming a 2000 char size of the field annotation (-1 for MAX)
                });

                dataAdapter.Fill(dataSet);
                conn.Close();
                return dataSet.Tables[0].Rows[0]["NextFolder"].ToString();

            }
        }


        public void SetOwner(string CurrentFolder, string Owner)
        {
            var dataSet = new DataSet();

            using (SqlConnection conn = new SqlConnection(connection))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("dbo.sp_SetOwner", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@CurrentFolder";
                param.Value = CurrentFolder;
                cmd.Parameters.Add(param);

                SqlParameter param2 = new SqlParameter();
                param2.ParameterName = "@Owner";
                param2.Value = Owner;
                cmd.Parameters.Add(param2);

                //cmd.Connection = conn;
                //cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
                conn.Close();

            }
        }

    }
}
